/**
 * Copyright 2005 SIB Framework

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
package org.sibframework.sda.reflect;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.sibframework.sda.SdaFactory;
import org.sibframework.sda.SimpleDataAccess;
import org.sibframework.sda.dbmodel.Column;
import org.sibframework.sda.dbmodel.Table;


public class OracleReflect implements IDBReflect {

	@Override
	public List<String> showSchemas() throws Exception {
		String sql = "select USERNAME from user_users";
		return SdaFactory.getInstance().queryStrings(sql);
	}
	
	@Override
	public List<Table> showTables(String schema) throws Exception {
		String sql = "select a.table_name, b.comments, a.num_rows, a.avg_row_len from all_tables a, user_tab_comments b where a.table_name = b.table_name";
		if (schema != null && schema.length() > 0) {
			sql += " and owner = upper('" + schema + "')";
		}
		sql += " order by TABLE_NAME";
		
		List<Map<String, Object>> ls = SdaFactory.getInstance().query2Map(sql);
		 List<Table> result = new ArrayList<Table>();
		for (Map<String, Object> map : ls) {
			Table table = new Table();
			table.setTableName((String) map.get("tableName"));
			table.setDescription((String) map.get("comments"));
			if (map.get("numRows") != null) {
				table.setNumRows(((BigDecimal) map.get("numRows")).longValue());
			}
			if (map.get("avgRowLen") != null) {
				table.setAvgRowLen(((BigDecimal)  map.get("avgRowLen")).longValue());
			}
			result.add(table);
		}
		return result;
	}
	
	@Override
	public List<Table> showTableColumns(String schema) throws Exception {

		//查询所有表
		String sql = "select a.table_name, b.comments, a.num_rows, a.avg_row_len from all_tables a, user_tab_comments b where a.table_name = b.table_name";
		if (schema != null && schema.length() > 0) {
			sql += " and owner = '" + schema.toUpperCase() + "'";
		}
		sql += " order by TABLE_NAME";		
		List<Map<String, Object>> ls = SdaFactory.getInstance().query2Map(sql);
		
		//查询所有列
		String sql2 = "select a.table_name,a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.NULLABLE, a.DATA_SCALE, a.DATA_PRECISION, b.comments, c.constraint_type" +
				" from user_tab_columns a, user_col_comments b, (" +
				"     select col.column_name, col.table_name, con.constraint_type from user_constraints con,  user_cons_columns col" +
				"     where con.constraint_name = col.constraint_name and con.table_name = col.table_name and con.constraint_type ='P'" +
				" ) c " +
				" where a.TABLE_NAME = b.table_name and a.COLUMN_NAME = b.column_name" +
				"      and c.table_name(+) = a.TABLE_NAME and c.column_name(+) = a.COLUMN_NAME";
		List<Map<String, Object>> cols = SdaFactory.getInstance().query2Map(sql2);

		//按照表来group列
		Map<String, List<Map<String, Object>>> colMap = new HashMap<String, List<Map<String,Object>>>();
		for (Map<String, Object> col: cols) {
			String tname = (String) col.get("tableName");
			List<Map<String, Object>> tcs = colMap.get(tname);
			
			if (tcs == null) {
				tcs = new ArrayList<Map<String,Object>>();
				colMap.put(tname, tcs);
			}
			
			tcs.add(col);
		}

		
		List<Table> result = new ArrayList<Table>();
		for (Map<String, Object> map : ls) {
			
			//table属性
			Table table = new Table();
			table.setTableName((String) map.get("tableName"));
			table.setDescription((String) map.get("comments"));
			if (map.get("numRows") != null) {
				table.setNumRows(((BigDecimal) map.get("numRows")).longValue());
			}
			if (map.get("avgRowLen") != null) {
				table.setAvgRowLen(((BigDecimal)  map.get("avgRowLen")).longValue());
			}
			
			//列
			Map<String, String> javaTypes = SdaFactory.getInstance().getColumnJavaType(table.getTableName());
			List<Column> columns = new ArrayList<Column>();
			List<Map<String, Object>> tcs = colMap.get(table.getTableName());
			for (Map<String, Object> colm: tcs) {
				Column col = new Column();
				col.setName((String) colm.get("columnName"));
				col.setType((String) colm.get("dataType"));
				col.setComments((String) colm.get("comments"));
				if (col.getComments() == null) {
					col.setComments("");
				}
				try {
					col.setNullable(colm.get("nullable").equals("Y") ? true : false);
					col.setLength((BigDecimal) colm.get("dataLength"));
					if (colm.get("dataPrecision") != null) {
						col.setPrecision(((BigDecimal)colm.get("dataPrecision")).intValue());
					}
					if (colm.get("dataScale") != null) {
						col.setScale(((BigDecimal)colm.get("dataScale")).intValue());
					}
					if (colm.get("constraintType") != null) {
						col.setPrimaryKey("P".equalsIgnoreCase((String) colm.get("constraintType")));
						if (table.getPkName() == null || table.getPkName().length() == 0) {
							table.setPkName(col.getName());
						} else {
							table.setPkName(table.getPkName() + "," + col.getName());
						}
					}
				} catch (Exception e) {
				}
				col.setJavaType(javaTypes.get(col.getName()));

				columns.add(col);
			}
			
			table.setCloumn(columns);			
			result.add(table);
		}

		return result;
	}
	
	@Override
	public List<Table> showTables() throws Exception {
		return this.showTables(null);
	}

	@Override
	public List<Column> showColumns(String tableName) throws Exception {
		
		Map<String, String> javaTypes = SdaFactory.getInstance().getColumnJavaType(tableName);
		//get fields from table
		List<Map<String, Object>> cos = SdaFactory.getInstance().query2Map(
				"select a.table_name,a.COLUMN_NAME, a.DATA_TYPE, a.DATA_LENGTH, a.NULLABLE, a.DATA_SCALE, a.DATA_PRECISION, b.comments from user_tab_columns a, user_col_comments b " +
				"where a.TABLE_NAME = b.table_name and a.COLUMN_NAME = b.column_name and a.table_name = upper('" + tableName + "')"
		);
		List<String> pks = SdaFactory.getInstance().queryStrings("select col.column_name from user_constraints con,  user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = upper('" + tableName + "')");

		List<Column> columns = new ArrayList<Column>();
		//fields declare.
		for (Map<String, Object> map : cos) {
			Column col = new Column();
			col.setName((String) map.get("columnName"));
			col.setType((String) map.get("dataType"));
			col.setComments((String) map.get("comments"));
			if (col.getComments() == null) {
				col.setComments("");
			}
			try {
				col.setNullable(map.get("nullable").equals("Y") ? true : false);
				col.setLength((BigDecimal) map.get("dataLength"));
				if (map.get("dataPrecision") != null) {
					col.setPrecision(((BigDecimal)map.get("dataPrecision")).intValue());
				}
				if (map.get("dataScale") != null) {
					col.setScale(((BigDecimal)map.get("dataScale")).intValue());
				}
			} catch (Exception e) {
			}
			col.setJavaType(javaTypes.get(col.getName()));
			if (pks.contains(col.getName())) {
				col.setPrimaryKey(true);
			}
			columns.add(col);
		}
		return columns;
	}

}
